﻿//
//文件名称：usersDAL.java
//文件描述： 数据库操作
//创 建 人：
//创建日期：
//-------------------------------------------------
package net.ycsk.dal;
import java.util.ArrayList;
import java.util.List;
import java.io.File;
 
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.text.SimpleDateFormat;
import net.ycsk.db.DBService; 
import net.ycsk.model.usersModel;
import net.ycsk.idal.usersIDAL;


public class usersDAL implements usersIDAL {

    private String SQL_Insert;		        
	private String SQL_Update_ByID;
	private String SQL_Delete_ByID;
	private String SQL_GetModel_ByID;
	private String SQL_GetList;
    private DBService dbService;
    private boolean isExists;
	private File dbFile;
	private SQLiteDatabase db;

	public usersDAL(Context context){
	     //设置sql命令
	     SQL_Insert = "INSERT INTO users(Name,age,birthday,Tel) values(?,?,?,?)";
		 SQL_Update_ByID = "UPDATE users SET Name=?,age=?,birthday=?,Tel=? WHERE userid = ?";
		 SQL_Delete_ByID = "DELETE FROM users WHERE userid = ?";
		 SQL_GetModel_ByID = "SELECT * FROM users WHERE userid = ?";
		 SQL_GetList = "SELECT * FROM users WHERE 0<=? ORDER BY userid DESC";//获取所有记录
	     
		 // --
		dbFile = new File("/sdcard/mengmatest.db");//注意：mengmatest.db是你导入sd卡的数据库文件名

		// 判断sd卡的数据库是否存在？
		// 请注意自行修改 DBService onCreate()方法中的数据库表格创建语句
		if (dbFile.exists()) {
			isExists = true;
			db = SQLiteDatabase.openOrCreateDatabase(dbFile, null);
		} else {
			dbService = new DBService(context);
			isExists = false;
		}
    }   
	//添加一条记录
	public void insert(usersModel model) {
		 Object[] args= new Object[]{model.getName(),model.getage(),model.getbirthday(),model.getTel()};
		 if (isExists) {
			db.execSQL(SQL_Insert, args);
		} else {
			dbService.execSQL(SQL_Insert, args);
		}
	}
	//修改一条记录
	public void update(usersModel model) {
		 Object[] args= new Object[]{model.getName(),model.getage(),model.getbirthday(),model.getTel(),model.getuserid()};
		if (isExists) {
			db.execSQL(SQL_Update_ByID, args);
		} else {
			dbService.execSQL(SQL_Update_ByID, args);
		}
	}
	//删除一条记录
	public void delete(Integer userid) {
		 Object[] args= new Object[]{userid};
		if (isExists) {
			db.execSQL(SQL_Delete_ByID, args);
		} else {
			dbService.execSQL(SQL_Delete_ByID, args);
		}
	}
    //根据主键获取一条记录
	public usersModel selectOne(Integer userid) {
		 String[] args= new String[]{userid.toString()};
		
		 usersModel model = new usersModel();
		 //model.setuserid(userid);
		
		 Cursor cursor = null;
		 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		 //Date date = sdf.parse("2008-08-08 12:10:12");
		 //日期转字符串		
		 //String date = sdf.format(new Date());
		 try{
		    if (isExists) {
				cursor = db.rawQuery(SQL_GetModel_ByID, args);
			} else {
				cursor = dbService.query(SQL_GetModel_ByID, args);
			} 
			 cursor.moveToFirst(); 	
			 if(cursor != null && cursor.getCount()==1){
			     			    			
				 				 model.setuserid(cursor.getInt(cursor.getColumnIndex("userid")));
				 model.setName(cursor.getString(cursor.getColumnIndex("Name")));
				 model.setage(cursor.getInt(cursor.getColumnIndex("age")));
				 //model.setbirthday(sdf.parse(cursor.getString(cursor.getColumnIndex("birthday"))));
				 model.setTel(cursor.getString(cursor.getColumnIndex("Tel")));

			 }
		}catch(Exception e){
			//e.printStatckTrace();
		}finally{
			if(cursor != null){
				cursor.close();
			}
		}
		return model;		
	}

	//根据页码获取所有记录
	public List<usersModel> selectAll(Integer pageNo) {		
		 List<usersModel> list =  new ArrayList<usersModel>();		 
		 String[] args= new String[]{pageNo.toString()};
		 Cursor cursor = null;
		 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		 try{
			if (isExists) {
				cursor = db.rawQuery(SQL_GetList, args);
			} else {
				cursor = dbService.query(SQL_GetList, args);
			}
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				 usersModel model = new usersModel();
				 
				 				 model.setuserid(cursor.getInt(cursor.getColumnIndex("userid")));
				 model.setName(cursor.getString(cursor.getColumnIndex("Name")));
				 model.setage(cursor.getInt(cursor.getColumnIndex("age")));
				 //model.setbirthday(sdf.parse(cursor.getString(cursor.getColumnIndex("birthday"))));
				 model.setTel(cursor.getString(cursor.getColumnIndex("Tel")));

					 					 
				 list.add(model);//加入集合
				 cursor.moveToNext();
			}
			 
		 }catch(Exception e){
				//e.printStatckTrace();
		}finally{
		    if(cursor != null){
				cursor.close();
			}
		}
		return list;		
	}

	//关闭数据库
	@Override
	public void closeDB() {
		if (db != null) {
			db.close();
		}
	}
}